﻿using bpms.Models;
using bpms.Utils;
using MySql.Data.MySqlClient;

namespace bpms.Dao
{
    /// <summary>
    /// 顾客数据持久层
    /// </summary>
    public class CustomerDao
    {
        /// <summary>
        /// 查询所有买家用户
        /// </summary>
        /// <returns></returns>
        public List<Customer> GetAll(string searchKey)
        {
            // 查询买家用户
            var sql = @"SELECT * FROM customer";
            if (!string.IsNullOrEmpty(searchKey))
            {
                sql += " where phone LIKE CONCAT('%', @searchKey, '%') or name LIKE CONCAT('%', @searchKey, '%')";
            }
            var CustomerList = MySqlDbHelper.QueryList<Customer>(
               sql,new MySqlParameter("@searchKey", searchKey)
                );
            return CustomerList;
        }

        /// <summary>
        /// 根据Id查询买家用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Customer GetById(int id)
        {
            // 查询买家用户
            var Customer = MySqlDbHelper.Query<Customer>(
                @"SELECT * FROM customer WHERE Id = @id",
                new MySqlParameter("@id", id)
                );
            return Customer;
        }

        /// <summary>
        /// 添加买家用户
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Customer model)
        {
            var insertSql = @"
                                insert into customer(login_name,password,name, phone, address, created_time,updated_time)
                                values(@LoginName, @Password, @Name, @Phone,@Address,now(),now())";
            var parameters = new[]
            {
                    new MySqlParameter("@LoginName", model.LoginName),
                    new MySqlParameter("@Password", PasswordHelper.MD5Encrypt(model.Password)),
                    new MySqlParameter("@Name", model.Name),
                    new MySqlParameter("@Phone",model.Phone),
                    new MySqlParameter("@Address",model.Address),
                };
            int cnt = MySqlDbHelper.Execute(insertSql, parameters);
            return cnt;
        }

        public int Update(Customer Customer)
        {
            Customer.Password = PasswordHelper.MD5Encrypt(Customer.Password);
            const string sql = @"UPDATE customer SET
                                            name = @name,
                                            password = @password,
                                            phone = @phone,
                                            address = @address,
                                            updated_time=now()
                                            WHERE id = @id";

            var parameters = new[] {
                                new MySqlParameter("@name", Customer.Name),
                                new MySqlParameter("@password", Customer.Password),
                                new MySqlParameter("@phone", Customer.Phone),
                                new MySqlParameter("@address", Customer.Address),
                                new MySqlParameter("@id", Customer.Id)
                    };

            int cnt = MySqlDbHelper.Execute(sql, parameters);
            return cnt;
        }

        /// <summary>
        /// 根据登录名查询买家用户
        /// </summary>
        /// <param name="loginName"></param>
        /// <returns></returns>
        public Customer GetByLoginName(string loginName)
        {
            // 查询买家用户
            var Customer = MySqlDbHelper.Query<Customer>(
                @"SELECT * FROM customer WHERE login_name = @loginName",
                new MySqlParameter("@loginName", loginName)
                );
            return Customer;
        }

        /// <summary>
        /// 根据登录名和密码查询买家用户
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public Customer GetByLoginNameAndPwd(string loginName, string password)
        {
            password= PasswordHelper.MD5Encrypt(password);
            // 查询买家用户
            var Customer = MySqlDbHelper.Query<Customer>(
                @"SELECT * FROM customer WHERE login_name = @loginName and Password=@password;",
                new MySqlParameter("@loginName", loginName),
                new MySqlParameter("@password", password)
                );
            return Customer;
        }

        public int Delete(int id)
        {
            var sql = "delete from customer where id=@id";
            return MySqlDbHelper.Execute(sql, new MySqlParameter("@id", id));
        }
    }
}
